﻿/********************************************************************************************************************
* Author:	Christos Polydorou																						*
* Email:	std04237@di.uoa.gr																						*
* Purpose:	This script is used in order to create the stored procedure "usp_entity_getattributevalues" that		*
*			returns the values for all ettributes of an entity.														*
********************************************************************************************************************/

CREATE PROCEDURE [dbo].[usp_entity_getattributevalues]
	@ownername nvarchar(50),
	@categoryname nvarchar(50),
	@entityname	nvarchar(50)
AS
	DECLARE @ownerid bigint
	DECLARE @categoryid bigint

	SELECT @ownerid = id											/* get the id of the owner of the category */
	FROM users
	WHERE users.username = @ownername

	SELECT @categoryid = id											/* get the id of the category */
	FROM Categories
	WHERE Categories.Name = @categoryname and Categories.Owner = @ownerid

	SELECT attributes.name,[entity-attribute].value
	FROM entities,attributes,[entity-attribute]
	WHERE entities.category = @categoryid and
		entities.name = @entityname and
		entities.category = attributes.category and
		[entity-attribute].entity = entities.id and
		[entity-attribute].attribute = attributes.id

RETURN 0